package com.b2c.repository.funds;

import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import com.alibaba.fastjson.JSONObject;
import com.b2c.entity.result.PagingResponse;
import com.b2c.entity.funds.OrderSettlementDetailPddVo;
import com.b2c.entity.funds.OrderSettlementPddEntity;
import com.b2c.entity.result.EnumResultVo;
import com.b2c.entity.result.ResultVo;

@Repository
public class OrderSettlementPddRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    private static Logger log = LoggerFactory.getLogger(OrderSettlementPddRepository.class);
    
    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    @Transactional
    public PagingResponse<OrderSettlementPddEntity> getList(Integer pageIndex, Integer pageSize, String orderSn, String startTime, String endTime) {
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS o.*,sh.`name` as shopName,sh.`type` as shopType  ");
        sb.append(" FROM dc_pdd_order_settlement as o");
        sb.append(" left join dc_shop sh on sh.id=o.shopId ");

        sb.append(" WHERE 1=1 ");
       
        if (StringUtils.isEmpty(orderSn) == false) {
            sb.append(" AND o.orderSn=? ");
            params.add(orderSn);
        }
    
        if (!StringUtils.isEmpty(startTime) ) {
            sb.append(" AND o.settlementTime >= ? ");
            params.add(startTime);
        }
        if (!StringUtils.isEmpty(endTime) ) {
            sb.append(" AND o.settlementTime <= ? ");
            params.add(endTime);
        }

    
        sb.append(" ORDER BY o.settlementTime DESC ");
        sb.append("  LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(OrderSettlementPddEntity.class), params.toArray(new Object[params.size()]));
        int totalSize = getTotalSize();
        return new PagingResponse<>(pageIndex, pageSize, totalSize, list);
    }

    @Transactional
    public ResultVo<String> importExcelSettlementList(List<OrderSettlementPddEntity> list, Integer shopId) {
        if (list == null || list.size() == 0)
            return new ResultVo<>(EnumResultVo.DataError, "参数错误：缺少list", "");

        /******* 插入数据 ********/
        int totalInsert = 0;// 新增数量
        int totalExist = 0;// 已存在数量
        int totalError = 0;// 错误数量
        for (var item : list) {
            try {
                // 查询数据是否存在
                var oList = jdbcTemplate.query(
                        "SELECT * FROM dc_pdd_order_settlement WHERE orderSn=? AND income=? AND expend=? AND type=? AND settlementTime=?",
                        new BeanPropertyRowMapper<>(OrderSettlementPddEntity.class), item.getOrderSn(),
                        item.getIncome(), item.getExpend(), item.getType(),item.getSettlementTime());
                if(oList !=null && oList.size()>0){
                    log.error("新增错误,已存在：" + JSONObject.toJSONString(item));
                    totalExist++;
                }else {
                    // 不存在添加，存在不添加

                    /************** 1、新增order **********************/
                    StringBuilder insertSQL = new StringBuilder();
                    insertSQL.append("INSERT INTO dc_pdd_order_settlement ");
                    insertSQL.append(" (orderSn,settlementTime,income,expend,type,remark,description,shopId) ");
                    insertSQL.append(" VALUE (?,?,?,?,?,?,?,?) ");

                    jdbcTemplate.update(insertSQL.toString(), item.getOrderSn(), item.getSettlementTime(),
                            item.getIncome(), item.getExpend(), item.getType(), item.getRemark(), item.getDescription(),
                            shopId);

                    /******************* 2、更新订单状态 **************************/
                    // 添加订单明细
                    String subSQL = "UPDATE dc_pdd_orders  SET settlementStatus=1 WHERE order_sn=? ";
                    jdbcTemplate.update(subSQL, item.getOrderSn());
                    // log.info("新增结算成功" + JSONObject.toJSONString(item));
                    totalInsert++;// 新增成功

                }
            } catch (Exception e) {
                totalError++;
                log.error("新增错误,系统异常：" + e.getMessage() + item.getOrderSn());

                return new ResultVo<>(EnumResultVo.SystemException, "系统异常：" + e.getMessage(), "");
            }
        }

        String msg = "新增成功：" + totalInsert + "，新增失败：" + totalError + "，已存在：" + totalExist;
        //// TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS", msg);

    }

    @Transactional
    public PagingResponse<OrderSettlementDetailPddVo> getOrderSettlementList(Integer pageIndex, Integer pageSize, String orderSn, String startTime, String endTime) {
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS order_sn,created_time AS orderTime,shipping_time,tracking_number,refund_status,order_status,discount_amount");
        sb.append(",pay_amount,auditStatus,settlementStatus,send_status");
        sb.append(",IFNULL((SELECT SUM(income) FROM dc_pdd_order_settlement WHERE orderSn = dc_pdd_orders.order_sn),0) AS income");
        sb.append(",IFNULL((SELECT SUM(expend) FROM dc_pdd_order_settlement WHERE orderSn = dc_pdd_orders.order_sn),0) AS expend");
        sb.append(" FROM dc_pdd_orders ");


        sb.append(" WHERE 1=1 ");
       
        if (StringUtils.isEmpty(orderSn) == false) {
            sb.append(" AND order_sn=? ");
            params.add(orderSn);
        }
    
        if (!StringUtils.isEmpty(startTime) ) {
            sb.append(" AND created_time >= ? ");
            params.add(startTime);
        }
        if (!StringUtils.isEmpty(endTime) ) {
            sb.append(" AND created_time <= ? ");
            params.add(endTime);
        }

    
        sb.append(" ORDER BY created_time DESC ");
        sb.append("  LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(OrderSettlementDetailPddVo.class), params.toArray(new Object[params.size()]));
        int totalSize = getTotalSize();
        return new PagingResponse<>(pageIndex, pageSize, totalSize, list);
    }

    @Transactional
    public List<OrderSettlementDetailPddVo> getOrderSettlementList(Integer shopId, String startTime, String endTime) {
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS order_sn,created_time AS orderTime,shipping_time,tracking_number,refund_status,order_status,discount_amount");
        sb.append(",pay_amount,auditStatus,settlementStatus,send_status");
        sb.append(",IFNULL((SELECT SUM(income) FROM dc_pdd_order_settlement WHERE orderSn = dc_pdd_orders.order_sn),0) AS income");
        sb.append(",IFNULL((SELECT SUM(expend) FROM dc_pdd_order_settlement WHERE orderSn = dc_pdd_orders.order_sn),0) AS expend");
        sb.append(" FROM dc_pdd_orders ");


        sb.append(" WHERE 1=1 ");
       

    
        if (!StringUtils.isEmpty(startTime) ) {
            sb.append(" AND created_time >= ? ");
            params.add(startTime);
        }
        if (!StringUtils.isEmpty(endTime) ) {
            sb.append(" AND created_time <= ? ");
            params.add(endTime);
        }

    
        sb.append(" ORDER BY created_time DESC ");
   
     

        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(OrderSettlementDetailPddVo.class), params.toArray(new Object[params.size()]));
        return list;
    }
}
